生成AIにスプレッドシートの集計方法を訪ねてみた結果

ちょっとした業務効率化が出来た記事です。

#Google Spreadsheet

#Microsoft Excel

#業務効率化

高崎渉

2024.05.31

こんにちは、高崎＠アノテーションです。

はじめに

スプレッドシートで業務関連の集計をしておりまして、効率的な集計はないものか、と弊社 Slack に導入されている生成 AI bot にたずねてみたらすんなり解決した話を記事にします。

Slack に導入されている生成 AI bot については下記のブログをご参照ください。

前提

こんな感じの集計表があったとして（詳細を記載すると NDA 抵触する可能性がありますので非常にざっくりした表ですが）、集計方法の遷移を順次記載していきます。

集計方法

1. 最初の集計方法

このシートとは別に建てたシート（例：「集計」）に品目の合計を計算する。

というのが最初の要件でした。

なお、「集計」シートは下記のような感じです。

ここは Excel でも経験があるので、そんなに難しくはなさそうで、

=SUMIF('記録'!B:B, B3, '記録'!C:C)

として最初の行はりんごを集計、その後下2つコピペして、

こんな感じで集計できますね。

2. 追加の集計要件

「集計」シートに品目の合計を計算するが、対象担当が記載されていないものは計算しない。

「集計」シートはこんなレイアウトです。

E 列に対象メンバーを記入し、記入されたメンバー限定で集計したい、というものです。

当初考えていたのが、SUMIFS を使って

=SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$3)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$4)

という感じなのですが、対象メンバーが増えるごとに書き足しが面倒ですし、いっそ、

=SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$3)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$4)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$5)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$6)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$7)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$8)+…

と、増加されるメンバーを多めに設定するという考えもそれ以上の対象メンバーが増えたときは一緒だし、良い感じのエレガントな集約方法は無いものか、と考えておりました。

いつもなら google センセイにお伺いするのですが、今回は冒頭に書いた生成 AI 機能を持つ Slack ボットに聞いてみようと思い、色々と会話してみました。

ほぉ。

=SUM(FILTER(C2:C9, (A2:A9="りんご") * (REGEXMATCH(B2:B9, TEXTJOIN("|", true, F2:F)))))

という提案を頂きましたので、これをちょっと改良し、

=SUM(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E)))))

で実行。

うまくいきました。

これに E 列にメンバーを追加すると、

こんな感じで値が変わりますし、一方でメンバーを減らすとこんな感じで減る…

(´・ω・｀)
あり。

FILTERでエラーになってそのまま表示出来なかったので、ISERRORを使って少し長くなりますが、

=IF(ISERROR(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E))))), 0, SUM(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E))))))

とFILTERのエラーを検知してやれば、

うまく行きましたね。

おまけの要件

「記録」シートを月ごとに整理したい

「記録」シートを例えば2024-05記録みたいなシート名にして月ごとに集計したい場合、シート名を単純に変えると上述の式のシート名の記載が変わってくれるのですが、月ごとに2024-05記録 2024-06記録…としたい時に、

この対象月のセル（赤枠）を変更しただけで集計したい、というものです。

毎回月が変わるごとにそれぞれのセルを対象月のシート名に変えるのは面倒ですよね？

ここは AI に尋ねるまでもなくINDIRECTを使えば解決。

=IF(ISERROR(FILTER(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!C:C"), (INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!B:B")=B5) * (REGEXMATCH(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!D:D"),TEXTJOIN("|", true, E:E))))), 0, SUM(FILTER(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!C:C"), (INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!B:B")=B5) * (REGEXMATCH(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!D:D"), TEXTJOIN("|", true, E:E))))))

…かなーり長い式になりましたが、同じような集計ができますし、シートを月ごとに変割った時に C2 セルの箇所を変更すれば、その月の集計ができる、という形で対応出来ました。